package gov.pbc.nn.core.utils;

import java.sql.ResultSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.bstek.bdf2.core.business.IDept;
import com.bstek.bdf2.core.business.IUser;
import com.bstek.bdf2.core.context.ContextHolder;
import com.bstek.bdf2.core.service.IDeptService;
import com.bstek.dorado.data.entity.EntityUtils;
import com.bstek.dorado.data.provider.Page;

import gov.pbc.nn.core.ICallback;
import gov.pbc.nn.core.bdf.entity.Dept;
import gov.pbc.nn.core.bdf.entity.User;
import gov.pbc.nn.core.bdf.service.DeptService.DefaultDeptRowMapper;
import gov.pbc.nn.core.bdf.service.UserService.DefaultUserRowMapper;
import gov.pbc.nn.core.dao.PbcnnCoreJdbcDao;

/**
 * 
 * @author louis
 * 
 */
public class SecurityUtils extends com.bstek.bdf2.core.security.SecurityUtils {

	/**
	 * 获取具有某个角色的所有用户
	 * 
	 * @param roleName
	 * @return 返回包含User对象的集合，每个User对象已经被包装成了dorado的Entity，而且含有deptId、deptName两个属性。
	 */
	public static List<IUser> getUserByRole(String roleName) {
		PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
		String procedureName = "getUserByRole";
		Map<Integer, Integer> inTypes = new HashMap<Integer, Integer>();
		inTypes.put(1, java.sql.Types.VARCHAR);
		inTypes.put(2, java.sql.Types.INTEGER);
		inTypes.put(3, java.sql.Types.INTEGER);
		Map<Integer, Object> inParams = new HashMap<Integer, Object>();
		inParams.put(1, roleName);
		inParams.put(2, 0);
		inParams.put(3, 0);
		List<IUser> users = jdbc.execProcedure(procedureName, 3, inTypes, inParams, null, null,
				new ICallback<ResultSet, IUser>() {

					@Override
					public IUser execute(ResultSet rs) {
						try {
							User user = EntityUtils.toEntity(EntityRowMapperUtils.get(User.class, rs, 0));
							EntityUtils.setValue(user, "deptId", rs.getString("ID_"));
							EntityUtils.setValue(user, "deptName", rs.getString("NAME_"));
							return user;
						} catch (Exception e) {
							throw new RuntimeException(e);
						}
					}

				});
		return users;
	}

	/**
	 * 获取具有某个角色的所有用户
	 * 
	 * @param roleName
	 * @param page
	 *            每个User对象已经被包装成了dorado的Entity，而且含有deptId、deptName两个属性。
	 */
	public static void getUserByRole(String roleName, final Page<IUser> page) {
		PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
		String procedureName = "getUserByRole";
		Map<Integer, Integer> inTypes = new HashMap<Integer, Integer>();
		inTypes.put(1, java.sql.Types.VARCHAR);
		inTypes.put(2, java.sql.Types.INTEGER);
		inTypes.put(3, java.sql.Types.INTEGER);
		Map<Integer, Object> inParams = new HashMap<Integer, Object>();
		inParams.put(1, roleName);
		inParams.put(2, page.getPageSize());
		inParams.put(3, page.getPageNo());
		page.setEntities(
				jdbc.execProcedure(procedureName, 3, inTypes, inParams, null, null, new ICallback<ResultSet, IUser>() {

					@Override
					public IUser execute(ResultSet rs) {
						try {
							User user = EntityUtils.toEntity(EntityRowMapperUtils.get(User.class, rs, 0));
							EntityUtils.setValue(user, "deptId", rs.getString("ID_"));
							EntityUtils.setValue(user, "deptName", rs.getString("NAME_"));
							page.setEntityCount(rs.getInt("COUNT_"));
							return user;
						} catch (Exception e) {
							throw new RuntimeException(e);
						}
					}

				}));
	}

	/**
	 * 获取某个部门中具有指定角色的人员（不包含其下级部门）
	 * 
	 * @param deptId
	 * @param roleName
	 * @return 返回包含User对象的集合，每个User对象已经被包装成了dorado的Entity，而且含有deptId、deptName两个属性。
	 */
	public static List<IUser> getUserByRoleForDept(String deptId, String roleName) {
		PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
		String procedureName = "getUserByRoleForDept";
		Map<Integer, Integer> inTypes = new HashMap<Integer, Integer>();
		inTypes.put(1, java.sql.Types.VARCHAR);
		inTypes.put(2, java.sql.Types.VARCHAR);
		inTypes.put(3, java.sql.Types.INTEGER);
		inTypes.put(4, java.sql.Types.INTEGER);
		inTypes.put(5, java.sql.Types.INTEGER);
		Map<Integer, Object> inParams = new HashMap<Integer, Object>();
		inParams.put(1, roleName);
		inParams.put(2, deptId);
		inParams.put(3, 0);
		inParams.put(4, 0);
		inParams.put(5, 0);
		List<IUser> users = jdbc.execProcedure(procedureName, 5, inTypes, inParams, null, null,
				new ICallback<ResultSet, IUser>() {

					@Override
					public IUser execute(ResultSet rs) {
						try {
							User user = EntityUtils.toEntity(EntityRowMapperUtils.get(User.class, rs, 0));
							EntityUtils.setValue(user, "deptId", rs.getString("ID_"));
							EntityUtils.setValue(user, "deptName", rs.getString("NAME_"));
							return user;
						} catch (Exception e) {
							throw new RuntimeException(e);
						}
					}

				});
		return users;
	}

	/**
	 * 获取某个部门中具有指定角色的人员（不包含其下级部门）
	 * 
	 * @param deptId
	 * @param roleName
	 * @param page
	 *            每个User对象已经被包装成了dorado的Entity，而且含有deptId、deptName两个属性。
	 */
	public static void getUserByRoleForDept(String deptId, String roleName, final Page<IUser> page) {
		PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
		String procedureName = "getUserByRoleForDept";
		Map<Integer, Integer> inTypes = new HashMap<Integer, Integer>();
		inTypes.put(1, java.sql.Types.VARCHAR);
		inTypes.put(2, java.sql.Types.VARCHAR);
		inTypes.put(3, java.sql.Types.INTEGER);
		inTypes.put(4, java.sql.Types.INTEGER);
		inTypes.put(5, java.sql.Types.INTEGER);
		Map<Integer, Object> inParams = new HashMap<Integer, Object>();
		inParams.put(1, roleName);
		inParams.put(2, deptId);
		inParams.put(3, 0);
		inParams.put(4, page.getPageSize());
		inParams.put(5, page.getPageNo());
		page.setEntities(
				jdbc.execProcedure(procedureName, 5, inTypes, inParams, null, null, new ICallback<ResultSet, IUser>() {

					@Override
					public IUser execute(ResultSet rs) {
						try {
							User user = EntityUtils.toEntity(EntityRowMapperUtils.get(User.class, rs, 0));
							EntityUtils.setValue(user, "deptId", rs.getString("ID_"));
							EntityUtils.setValue(user, "deptName", rs.getString("NAME_"));
							page.setEntityCount(rs.getInt("COUNT_"));
							return user;
						} catch (Exception e) {
							throw new RuntimeException(e);
						}
					}

				}));
	}

	/**
	 * 获取某个部门中具有指定角色的人员（包含其下级部门）
	 * 
	 * @param deptId
	 * @param roleName
	 * @return 返回包含User对象的集合，每个User对象已经被包装成了dorado的Entity，而且含有deptId、deptName两个属性。
	 */
	public static List<IUser> getUserByRoleForDeptWithChildren(String deptId, String roleName) {
		PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
		String procedureName = "getUserByRoleForDept";
		Map<Integer, Integer> inTypes = new HashMap<Integer, Integer>();
		inTypes.put(1, java.sql.Types.VARCHAR);
		inTypes.put(2, java.sql.Types.VARCHAR);
		inTypes.put(3, java.sql.Types.INTEGER);
		inTypes.put(4, java.sql.Types.INTEGER);
		inTypes.put(5, java.sql.Types.INTEGER);
		Map<Integer, Object> inParams = new HashMap<Integer, Object>();
		inParams.put(1, roleName);
		inParams.put(2, deptId);
		inParams.put(3, 1);
		inParams.put(4, 0);
		inParams.put(5, 0);
		List<IUser> users = jdbc.execProcedure(procedureName, 5, inTypes, inParams, null, null,
				new ICallback<ResultSet, IUser>() {

					@Override
					public IUser execute(ResultSet rs) {
						try {
							User user = EntityUtils.toEntity(EntityRowMapperUtils.get(User.class, rs, 0));
							EntityUtils.setValue(user, "deptId", rs.getString("ID_"));
							EntityUtils.setValue(user, "deptName", rs.getString("NAME_"));
							return user;
						} catch (Exception e) {
							throw new RuntimeException(e);
						}
					}

				});
		return users;
	}

	/**
	 * 获取某个部门中具有指定角色的人员（包含其下级部门）
	 * 
	 * @param deptId
	 * @param roleName
	 * @param page
	 *            每个User对象已经被包装成了dorado的Entity，而且含有deptId、deptName两个属性。
	 */
	public static void getUserByRoleForDeptWithChildren(String deptId, String roleName, final Page<IUser> page) {
		PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
		String procedureName = "getUserByRoleForDept";
		Map<Integer, Integer> inTypes = new HashMap<Integer, Integer>();
		inTypes.put(1, java.sql.Types.VARCHAR);
		inTypes.put(2, java.sql.Types.VARCHAR);
		inTypes.put(3, java.sql.Types.INTEGER);
		inTypes.put(4, java.sql.Types.INTEGER);
		inTypes.put(5, java.sql.Types.INTEGER);
		Map<Integer, Object> inParams = new HashMap<Integer, Object>();
		inParams.put(1, roleName);
		inParams.put(2, deptId);
		inParams.put(3, 1);
		inParams.put(4, page.getPageSize());
		inParams.put(5, page.getPageNo());
		page.setEntities(
				jdbc.execProcedure(procedureName, 5, inTypes, inParams, null, null, new ICallback<ResultSet, IUser>() {

					@Override
					public IUser execute(ResultSet rs) {
						try {
							User user = EntityUtils.toEntity(EntityRowMapperUtils.get(User.class, rs, 0));
							EntityUtils.setValue(user, "deptId", rs.getString("ID_"));
							EntityUtils.setValue(user, "deptName", rs.getString("NAME_"));
							page.setEntityCount(rs.getInt("COUNT_"));
							return user;
						} catch (Exception e) {
							throw new RuntimeException(e);
						}
					}

				}));
	}

	/**
	 * 根据指定的部门ID获取该部门所属中支中具有指定角色的用户。
	 * 
	 * @param deptId
	 * @param roleName
	 * @return 返回包含User对象的集合，每个User对象已经被包装成了dorado的Entity，而且含有deptId、deptName两个属性。
	 */
	public static List<IUser> getUserByRoleForZZ(String deptId, String roleName) {
		PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
		String procedureName = "getUserByRoleForZZ";
		Map<Integer, Integer> inTypes = new HashMap<Integer, Integer>();
		inTypes.put(1, java.sql.Types.VARCHAR);
		inTypes.put(2, java.sql.Types.VARCHAR);
		inTypes.put(3, java.sql.Types.INTEGER);
		inTypes.put(4, java.sql.Types.INTEGER);
		Map<Integer, Object> inParams = new HashMap<Integer, Object>();
		inParams.put(1, roleName);
		inParams.put(2, deptId);
		inParams.put(3, 0);
		inParams.put(4, 0);
		List<IUser> users = jdbc.execProcedure(procedureName, 4, inTypes, inParams, null, null,
				new ICallback<ResultSet, IUser>() {

					@Override
					public IUser execute(ResultSet rs) {
						try {
							User user = EntityUtils.toEntity(EntityRowMapperUtils.get(User.class, rs, 0));
							EntityUtils.setValue(user, "deptId", rs.getString("ID_"));
							EntityUtils.setValue(user, "deptName", rs.getString("NAME_"));
							return user;
						} catch (Exception e) {
							throw new RuntimeException(e);
						}
					}

				});
		return users;
	}

	/**
	 * 根据指定的部门ID获取该部门所属中支中具有指定角色的用户。
	 * 
	 * @param deptId
	 * @param roleName
	 * @param page
	 *            每个User对象已经被包装成了dorado的Entity，而且含有deptId、deptName两个属性。
	 */
	public static void getUserByRoleForZZ(String deptId, String roleName, final Page<IUser> page) {
		PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
		String procedureName = "getUserByRoleForZZ";
		Map<Integer, Integer> inTypes = new HashMap<Integer, Integer>();
		inTypes.put(1, java.sql.Types.VARCHAR);
		inTypes.put(2, java.sql.Types.VARCHAR);
		inTypes.put(3, java.sql.Types.INTEGER);
		inTypes.put(4, java.sql.Types.INTEGER);
		Map<Integer, Object> inParams = new HashMap<Integer, Object>();
		inParams.put(1, roleName);
		inParams.put(2, deptId);
		inParams.put(3, page.getPageSize());
		inParams.put(4, page.getPageNo());
		page.setEntities(
				jdbc.execProcedure(procedureName, 4, inTypes, inParams, null, null, new ICallback<ResultSet, IUser>() {

					@Override
					public IUser execute(ResultSet rs) {
						try {
							User user = EntityUtils.toEntity(EntityRowMapperUtils.get(User.class, rs, 0));
							EntityUtils.setValue(user, "deptId", rs.getString("ID_"));
							EntityUtils.setValue(user, "deptName", rs.getString("NAME_"));
							page.setEntityCount(rs.getInt("COUNT_"));
							return user;
						} catch (Exception e) {
							throw new RuntimeException(e);
						}
					}

				}));
	}

	/**
	 * 判断某个用户是否具有指定的角色
	 * 
	 * @param username
	 * @param roleName
	 * @return
	 */
	public static boolean containsRole(String username, String roleName) {
		for (IUser u : getUserByRole(roleName)) {
			if (u.getUsername().equals(username)) {
				return true;
			}
		}
		return false;
	}

	/**
	 * 判断某个用户在某部门中是否具有指定的角色
	 * 
	 * @param username
	 * @param deptId
	 * @param roleName
	 * @return
	 */
	public static boolean containsRoleInDept(String username, String deptId, String roleName) {
		for (IUser u : getUserByRoleForDept(deptId, roleName)) {
			if (u.getUsername().equals(username)) {
				return true;
			}
		}
		return false;
	}

	/**
	 * 判断某个用户在某部门及其子孙部门中是否具有指定的角色
	 * 
	 * @param username
	 * @param deptId
	 * @param roleName
	 * @return
	 */
	public static boolean containsRoleInDeptWithChildren(String username, String deptId, String roleName) {
		for (IUser u : getUserByRoleForDeptWithChildren(deptId, roleName)) {
			if (u.getUsername().equals(username)) {
				return true;
			}
		}
		return false;
	}

	/**
	 * 获取具有某个岗位的所有用户
	 * 
	 * @param positionName
	 * @return
	 */
	public static List<IUser> getUserByPosition(String positionName) {
		String sql = "SELECT\n" + "	U.*\n" + "FROM\n" + "	T_USER U\n"
				+ "INNER JOIN BDF2_USER_POSITION UP ON U.USERNAME_ = UP.USERNAME_\n"
				+ "INNER JOIN BDF2_POSITION P ON UP.POSITION_ID_ = P.ID_\n" + "WHERE\n" + "	P.NAME_ = ?";
		PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
		List<IUser> users = jdbc.getJdbcTemplate().query(sql, new Object[] { positionName },
				new DefaultUserRowMapper());
		return users;
	}

	/**
	 * 获取某个部门中具有指定岗位的人员
	 * 
	 * @param positionName
	 * @return
	 */
	public static List<IUser> getUserByPositionForDept(String deptId, String positionName) {
		String sql = "SELECT\n" + "	U.*\n" + "FROM\n" + "	T_USER U\n"
				+ "INNER JOIN BDF2_USER_POSITION UP ON U.USERNAME_ = UP.USERNAME_\n"
				+ "INNER JOIN BDF2_POSITION P ON UP.POSITION_ID_ = P.ID_\n"
				+ "INNER JOIN BDF2_USER_DEPT UD ON UD.USERNAME_ = U.USERNAME_\n" + "WHERE\n" + "	P.NAME_ = ?\n"
				+ "AND UD.DEPT_ID_ = ?";
		PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
		List<IUser> users = jdbc.getJdbcTemplate().query(sql, new Object[] { positionName, deptId },
				new DefaultUserRowMapper());
		return users;
	}

	/**
	 * 根据指定的部门ID获取该部门所属中支中具有指定岗位的用户。
	 * 
	 * @param deptId
	 * @param roleName
	 * @return
	 */
	public static List<IUser> getUserByPositioinForZZ(String deptId, String positionName) {
		Dept dept = (Dept) getZZByDept(deptId);
		String sql = "SELECT\n" + "	U.*\n" + "FROM\n" + "	T_USER U\n"
				+ "INNER JOIN BDF2_USER_POSITION UP ON U.USERNAME_ = UP.USERNAME_\n"
				+ "INNER JOIN BDF2_POSITION P ON UP.POSITION_ID_ = P.ID_\n"
				+ "INNER JOIN BDF2_USER_DEPT UD ON UD.USERNAME_ = U.USERNAME_\n" + "WHERE\n" + "	P.NAME_ = ?\n"
				+ "AND UD.DEPT_ID_ LIKE CONCAT(SUBSTR(? FROM 1 FOR 9),'%')\n";
		PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
		List<IUser> users = jdbc.getJdbcTemplate().query(sql, new Object[] { positionName, dept.getId() },
				new DefaultUserRowMapper());
		return users;
	}

	/**
	 * 判断某人是否具有某个岗位。返回true则具有指定岗位
	 * 
	 * @param username
	 * @param positionName
	 * @return
	 */
	public static boolean containsPosition(String username, String positionName) {
		String sql = "SELECT\n" + "	count(*)\n" + "FROM\n" + "	T_USER U\n"
				+ "INNER JOIN BDF2_USER_POSITION UP ON U.USERNAME_ = UP.USERNAME_\n"
				+ "INNER JOIN BDF2_POSITION P ON UP.POSITION_ID_ = P.ID_\n" + "WHERE\n"
				+ "	P.NAME_ = ? AND U.USERNAME_=?";
		PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
		Number number = jdbc.getJdbcTemplate().queryForObject(sql, new Object[] { positionName, username },
				Number.class);
		return number.intValue() > 0;
	}

	/**
	 * 根据用户username获取该用户所处的机构
	 * 
	 * @param username
	 * @return
	 */
	// public static IDept getOrgDept(String username) {
	// String sql = "SELECT\n" + " d.*\n" + " FROM\n" + " t_dept d\n"
	// + " INNER JOIN bdf2_user_dept ud ON d.ID_ = ud.DEPT_ID_\n" + " WHERE\n" +
	// " ud.USERNAME_ = ?";
	// PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
	// List<IDept> depts = jdbc.getJdbcTemplate().query(sql, new String[] {
	// username }, new DefaultDeptRowMapper());
	// if (depts.isEmpty()) {
	// return null;
	// }
	// Dept dept = (Dept) depts.get(0);
	// // return getOrgByDept(dept);
	// return null;
	// }

	// /**
	// * 找到给定部门所属的机构
	// *
	// * @param deptId
	// * @return
	// */
	// public static IDept getOrgByDept(String deptId) {
	// String sql = "select * from t_dept d where d.ID_=?";
	// PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
	// Dept dept = (Dept) jdbc.getJdbcTemplate().queryForObject(sql, new
	// Object[] { deptId },
	// new DefaultDeptRowMapper());
	// return getOrgByDept(dept);
	// }

	// /**
	// * 找到给定部门所属的机构
	// *
	// * @param deptId
	// * @return
	// */
	// public static IDept getOrgByDept(IDept dept) {
	// String sql = "select * from t_dept d where d.ID_=?";
	// PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
	// Dept d = (Dept) dept;
	// while (!Dept.ROOT.equals(d.getParentId())) {
	// d = (Dept) jdbc.getJdbcTemplate().queryForObject(sql, new Object[] {
	// dept.getParentId() },
	// new DefaultDeptRowMapper());
	// }
	// return d;
	// }
	//
	// /**
	// * 找到指定部门所属的中支
	// *
	// * @param deptId
	// * @return
	// */
	// public static IDept getZZByDept(IDept dept) {
	// String sql = "select * from t_dept d where d.ID_=?";
	// PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
	// Dept d = (Dept) dept;
	// if (d.getLevel() == 0) {
	// if ("1".equals(d.getDeptLevel())) {
	// return d;
	// } else {
	// d = (Dept) jdbc.getJdbcTemplate().queryForObject(sql, new Object[] {
	// Dept.NANNING },
	// new DefaultDeptRowMapper());
	// }
	// } else if (d.getLevel() == 1) {
	// d = (Dept) jdbc.getJdbcTemplate().queryForObject(sql, new Object[] {
	// Dept.NANNING },
	// new DefaultDeptRowMapper());
	// } else if (d.getLevel() == 2) {
	// if (Dept.ROOT.equals(d.getParentId())) {
	// return d;
	// } else {
	// while (!Dept.ROOT.equals(d.getParentId())) {
	// d = (Dept) jdbc.getJdbcTemplate().queryForObject(sql, new Object[] {
	// d.getParentId() },
	// new DefaultDeptRowMapper());
	// }
	// }
	// } else if (d.getLevel() == 3) {
	// String id = StringUtils.substring(d.getId(), 0, 9);
	// d = (Dept) jdbc.getJdbcTemplate().queryForObject(sql, new Object[] { id
	// }, new DefaultDeptRowMapper());
	// }
	// return d;
	// }
	/**
	 * 找到指定部门所属的中支
	 * 
	 * @param deptId
	 * @return
	 */
	public static IDept getZZByDept(IDept dept) {
		if (dept.getId().length() == 0) {
			return dept;
		}
		return getZZByDept(dept.getId());
	}

	/**
	 * 获取指定用户所属的中支
	 * 
	 * @param username
	 * @return
	 */
	public static IDept getZZByUser(String username) {
		IDeptService ds = ContextHolder.getBean(IDeptService.BEAN_ID);
		return getZZByDept(ds.loadUserDepts(username).get(0));
	}

	/**
	 * 找到指定部门所属的中支
	 * 
	 * @param deptId
	 * @return
	 */
	public static IDept getZZByDept(String deptId) {
		PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
		String sql = "select * from t_dept d where d.ID_=?";
		Dept dept = (Dept) jdbc.getJdbcTemplate().queryForObject(sql,
				new Object[] { StringUtils.substring(deptId, 0, 9) }, new DefaultDeptRowMapper());
		return dept;
	}
}
